/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.clarice.homechef.dao;

import com.clarice.homechef.domain.Meal;
import com.clarice.homechef.domain.MealReview;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

/**
 *
 * @author Akshay Velhal
 */
@Repository
public class MealDaoImpl implements MealDao {

    @Autowired
    DataSource dataSource;
    private static String sqlCreateNewMeal = "insert into meal (name, price, mealitems, chefid, address, mealtype, chefspecial, description, quantity) values (?,?,?,?,?,?,?,?,?)";

    public Meal createNewMeal(Meal meal, String username) throws Exception {
        int newMealId = -1;
        try (Connection conn = dataSource.getConnection();
                PreparedStatement ps = conn.prepareStatement(sqlCreateNewMeal, PreparedStatement.RETURN_GENERATED_KEYS);) {

            ps.setString(1, meal.getMealName());
            ps.setDouble(2, meal.getPrice());
            ps.setString(3, meal.getMealItems());
            ps.setInt(4, meal.getChefId());
            ps.setString(5, meal.getAddress());
            ps.setString(6, meal.getMealType());
            ps.setBoolean(7, meal.isChefSpecial());
            ps.setString(8, meal.getDescription());
            ps.setInt(9, meal.getQuantity());
            ps.executeUpdate();
            ResultSet generatedKeys = ps.getGeneratedKeys();
            while (generatedKeys.next()) {
                newMealId = generatedKeys.getInt("mealid");
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            throw ex;
        }
        return getMeal(newMealId);
    }
    private static String sqlGetMeal = " with chefrating as (select chefid,avg(rating), count(1) as noofreviews "
            + "                     from meal A  "
            + "                     join chef B using (chefid)  "
            + "                     left outer join mealreview C using (mealid)  "
            + "                     where mealid = ? "
            + "                     group by chefid),  "
            + " mealorders as (select mealid, sum(A.quantity) as ordered from orders A  "
            + "                     join meal B using(mealid)  "
            + "                     where mealid = ? "
            + "                     group by mealid ) "
            + " select mealid, name, price, mealitems, chefid, address, createdon, mealtype, chefspecial, "
            + " description, quantity, quantity - Coalesce(ordered, 0) as availablequantity, avg as rating, noofreviews "
            + " from meal  "
            + " left outer join chefrating using (chefid) "
            + " left outer join mealorders using (mealid) "
            + " where mealid = ? ";

    public Meal getMeal(int mealId) throws Exception {
        Meal meal = new Meal();
        try (Connection conn = dataSource.getConnection();
                PreparedStatement ps = conn.prepareStatement(sqlGetMeal);) {
            ps.setInt(1, mealId);
            ps.setInt(2, mealId);
            ps.setInt(3, mealId);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                meal.setMealId(rs.getInt("mealid"));
                meal.setMealName(rs.getString("name"));
                meal.setPrice(rs.getDouble("price"));
                meal.setMealItems(rs.getString("mealitems"));
                meal.setChefId(rs.getInt("chefid"));
                meal.setAddress(rs.getString("address"));
                meal.setCreatedOn(rs.getString("createdon"));
                meal.setMealType(rs.getString("mealtype"));
                meal.setChefSpecial(rs.getBoolean("chefspecial"));
                meal.setDescription(rs.getString("description"));
                meal.setQuantity(rs.getInt("quantity"));
                meal.setAvailableQuantity(rs.getInt("availablequantity"));
                meal.setRating(rs.getDouble("rating"));
                meal.setNoofreviews(rs.getInt("noofreviews"));
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            throw ex;
        }
        return meal;
    }
    private static String sqlGetMeals = " with chefrating as (select chefid,avg(rating), count(1) as noofreviews "
            + " from meal A  "
            + " join chef B using (chefid) "
            + " left outer join mealreview C using (mealid) "
            + " where createdon::date = (?)::date "
            + " group by chefid), "
            + " mealorders as (select mealid, sum(A.quantity) as ordered from orders A "
            + " join meal B using(mealid) "
            + " where createdon::date = (?)::date "
            + " group by mealid "
            + " ) "
            + " select chefid, mealid, A.name as mealname, price, mealitems,A.area as mealarea, "
            + " A.address as mealaddress, createdon::date, mealtype, chefspecial, description, quantity, "
            + " B.name as chefname, email, B.area as chefarea, B.address as chefaddress, mobile, avg  as rating , "
            + " A.quantity - Coalesce(ordered, 0) as availablequantity, noofreviews, quantity - Coalesce(ordered, 0) as availablequantity "
            + " from meal A "
            + " join chef B using (chefid) "
            + " left outer join chefrating using (chefid) "
            + " left outer join mealorders using (mealid) "
            + " where createdon::date = (?)::date "
            + " and A.quantity - Coalesce(ordered, 0) > 0 ";

    public List<Meal> getMeals(String date) throws Exception {
        List<Meal> meals = new ArrayList<>();
        try (Connection conn = dataSource.getConnection();
                PreparedStatement ps = conn.prepareStatement(sqlGetMeals);) {
            ps.setString(1, date);
            ps.setString(2, date);
            ps.setString(3, date);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Meal meal = new Meal();
                meal.setChefId(rs.getInt("chefid"));
                meal.setMealId(rs.getInt("mealid"));
                meal.setMealName(rs.getString("mealname"));
                meal.setPrice(rs.getDouble("price"));
                meal.setMealItems(rs.getString("mealitems"));
                meal.setMealArea(rs.getString("mealarea"));
                meal.setMealAddress(rs.getString("mealaddress"));
                meal.setCreatedOn(rs.getString("createdon"));
                meal.setMealType(rs.getString("mealtype"));
                meal.setChefSpecial(rs.getBoolean("chefspecial"));
                meal.setDescription(rs.getString("description"));
                meal.setQuantity(rs.getInt("quantity"));
                meal.setChefName(rs.getString("chefname"));
                meal.setChefEmail(rs.getString("email"));
                meal.setChefArea(rs.getString("chefarea"));
                meal.setChefAddress(rs.getString("chefaddress"));
                meal.setChefMobile(rs.getString("mobile"));
                meal.setRating(rs.getDouble("rating"));
                meal.setAvailableQuantity(rs.getInt("availablequantity"));
                meal.setNoofreviews(rs.getInt("noofreviews"));
                meals.add(meal);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            throw ex;
        }
        return meals;
    }
    private static String sqlGetPopularDishes = "With ratings as ( "
            + " select mealid,avg(rating) as ratings from mealreview "
            + " where mealid != ? "
            + " group by mealid) "
            + " select A.name as mealname, ratings, price "
            + " from meal A "
            + " join chef B using (chefid) "
            + " left outer join ratings using (mealid) "
            + " where mealid != ? "
            + " and chefid = (select chefid  "
            + " from meal where mealid = ?) limit 5";

    public List<Meal> getPopularDishes(int mealId) throws Exception {
        List<Meal> meals = new ArrayList<>();
        try (Connection conn = dataSource.getConnection();
                PreparedStatement ps = conn.prepareStatement(sqlGetPopularDishes);) {
            ps.setInt(1, mealId);
            ps.setInt(2, mealId);
            ps.setInt(3, mealId);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Meal meal = new Meal();
                meal.setMealName(rs.getString("mealname"));
                meal.setRating(rs.getDouble("ratings"));
                meal.setPrice(rs.getDouble("price"));
                meals.add(meal);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            throw ex;
        }
        return meals;
    }
    private static String sqlGetLatestTrendingDishes = "With ratings as ( "
            + " select mealid,avg(rating) as ratings from mealreview "
            + " where mealid != ? "
            + " group by mealid) "
            + " select A.name as name, ratings, price "
            + " from meal A "
            + " join chef B using (chefid) "
            + " left outer join ratings using (mealid) "
            + " where mealid != ? "
            + " order by ratings desc limit 5";

    public List<Meal> getLatestTrendingDishes(int mealId) throws Exception {
        List<Meal> meals = new ArrayList<>();
        try (Connection conn = dataSource.getConnection();
                PreparedStatement ps = conn.prepareStatement(sqlGetLatestTrendingDishes);) {
            ps.setInt(1, mealId);
            ps.setInt(2, mealId);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                Meal meal = new Meal();
                meal.setMealName(rs.getString("name"));
                meal.setRating(rs.getDouble("ratings"));
                meal.setPrice(rs.getDouble("price"));
                meals.add(meal);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            throw ex;
        }
        return meals;
    }
    
    private static String sqlInsertMealReview = "insert into mealreview (mealid, review, rating) values (?, ?, ?);";
    public void addMealReview(int mealId, MealReview mealReview) throws Exception {
        try (Connection conn = dataSource.getConnection();
                PreparedStatement ps = conn.prepareStatement(sqlInsertMealReview);) {
            ps.setInt(1, mealId);
            ps.setString(2, mealReview.getReviews());
            ps.setInt(3, mealReview.getRatings());
            ps.executeUpdate();
        } catch (Exception ex) {
            ex.printStackTrace();
            throw ex;
        }
    }
}
